﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Model.Data;
using Wedn.Utility;

namespace Wedn.DAL
{
    /// <summary>
    /// 文章表操作类(初步审核通过)
    /// 此类为抽象类, 不允许被实例化, 需要使用时直接调用
    /// </summary>
    public abstract class PostsDAO
    {
        #region 增加文章
        /// <summary>
        /// 向文章表中插入一行数据, 返回执行受影响行数
        /// </summary>
        /// <param name="post">要插入的对象</param>
        /// <returns>受影响行数</returns>
        public static int Insert(Post post)
        {
            string sql = @"INSERT INTO [Blog_Posts]
                                    (
                                        [post_alias],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_comment_count],
                                        [post_view_count],
                                        [post_auther_id],
                                        [post_status],
                                        [post_view_level],
                                        [post_password]
                                    )
                                    VALUES 
                                    ( 
                                        @Alias,
                                        @Title,
                                        @SubTitle,
                                        @PubDate,
                                        @Update,
                                        @Content,
                                        @CommenCount,
                                        @ViewCount,
                                        @AutherId,
                                        @Status,
                                        @ViewLevel,
                                        @Password
                                    );";
            return ExecuteNonQuery(post, sql);
        }

        #endregion

        #region 删除文章

        #region 删除一条数据
        /// <summary>
        /// 根据id删除对应的文章记录(包括旗下所有评论),返回受影响行数
        /// </summary>
        /// <param name="postid">要删除记录的ID</param>
        /// <returns>受影响行数</returns>
        public static int Delete(int postid)
        {
            //注意这种T-SQL写法,也是可行的,但是不能少分号哦!
            string sql = @"DELETE FROM [Blog_Posts] WHERE [post_id]=@Id;
                                   DELETE FROM [Blog_Comments] WHERE [comment_post_id]=@Id;";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", postid));
        }
        #endregion

        #region 删除多条数据
        /// <summary>
        /// 根据id数组删除对应的文章记录(包括旗下所有评论),返回受影响行数
        /// </summary>
        /// <param name="ids">要删除记录的ID字符串,以逗号分隔"1,2,3"</param>
        /// <returns>受影响行数</returns>
        public static int Delete(string ids)
        {
            //注意这种T-SQL写法,也是可行的,但是不能少分号哦!
            string sql = "DELETE FROM [Blog_Posts] WHERE [post_id] in (" + ids + "); DELETE FROM [Blog_Comments] WHERE [comment_post_id] in (" + ids + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }
        #endregion

        #endregion

        #region 更新文章
        /// <summary>
        /// 根据用户对象的ID属性更新用户信息, 返回受影响行数
        /// </summary>
        /// <param name="post">要更新的用户对象</param>
        /// <returns>受影响行数</returns>
        public static int Update(Post post)
        {
            string sql = @"UPDATE [Blog_Posts]
                                   SET
                                        [post_alias]=@Alias,
                                        [post_title]=@Title,
                                        [post_subtitle]=@SubTitle,
                                        [post_pubdate]=@PubDate,
                                        [post_update]=@Update,
                                        [post_content]=@Content,
                                        [post_comment_count]=@CommenCount,
                                        [post_view_count]=@ViewCount,
                                        [post_auther_id]=@AutherId,
                                        [post_status]=@Status,
                                        [post_view_level]=@ViewLevel,
                                        [post_password]=@Password
                                    WHERE 
                                        [post_id]=@Id;";
            return ExecuteNonQuery(post, sql);
        }
        #endregion

        #region 选取文章

        #region 获取表中主键最大值
        /// <summary>
        /// 获取表中主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public static int GetMaxId()
        {
            return SqlHelper.SelectMaxId("post_id", "Blog_Posts");
        }
        #endregion

        #region 获取所有文章信息集合
        /// <summary>
        /// 获取文章信息表中所有文章信息, 返回对象集合
        /// </summary>
        /// <returns>所有文章信息集合</returns>
        public static List<Post> SelectPosts()
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [post_id],
                                        [post_alias],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_comment_count],
                                        [post_view_count],
                                        [post_auther_id],
                                        [post_status],
                                        [post_view_level],
                                        [post_password]
                                   FROM
                                        [Blog_Posts];";
            return Select(sql);
        }
        #endregion

        #region 根据指定字段和是否降序排序从文章信息表中获取指定条数文章信息, 返回对象集合
        /// <summary>
        /// 根据指定字段和是否降序排序从文章信息表中获取指定条数文章信息, 返回对象集合
        /// </summary>
        /// <param name="pageSize">文章数目</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isDescending">是否降序</param>
        /// <returns>符合条件文章信息集合</returns>
        public static List<Post> SelectPosts(int pageSize, string orderField, bool isDescending)
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = string.Format(@"SELECT TOP {0}
                                                                [post_id],
                                                                [post_alias],
                                                                [post_title],
                                                                [post_subtitle],
                                                                [post_pubdate],
                                                                [post_update],
                                                                [post_content],
                                                                [post_comment_count],
                                                                [post_view_count],
                                                                [post_auther_id],
                                                                [post_status],
                                                                [post_view_level],
                                                                [post_password]
                                                           FROM
                                                                [Blog_Posts]
                                                           ORDER BY
                                                                {1}", pageSize, orderField);
            if (isDescending)
                sql += " DESC";
            return Select(sql);
        }
        #endregion

        #region 选择多条文章
        /// <summary>
        /// 根据ID数据批量获取文章信息集合, 返回对象集合
        /// </summary>
        /// <param name="ids">ID字符串,以逗号分隔"1,2,3...."</param>
        /// <returns>多条文章信息集合</returns>
        public static List<Post> SelectPosts(string ids)
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [post_id],
                                        [post_alias],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_comment_count],
                                        [post_view_count],
                                        [post_auther_id],
                                        [post_status],
                                        [post_view_level],
                                        [post_password]
                                   FROM
                                        [Blog_Posts]
                                   WHERE
                                        [post_id] in (" + ids + ");";
            return Select(sql);
        }
        #endregion

        #region 选择一条数据
        /// <summary>
        /// 根据文章ID获取文章信息对象, 返回文章对象
        /// </summary>
        /// <param name="postid">文章ID</param>
        /// <returns>文章对象</returns>
        public static Post SelectPost(int postid)
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [post_id],
                                        [post_alias],
                                        [post_title],
                                        [post_subtitle],
                                        [post_pubdate],
                                        [post_update],
                                        [post_content],
                                        [post_comment_count],
                                        [post_view_count],
                                        [post_auther_id],
                                        [post_status],
                                        [post_view_level],
                                        [post_password]
                                   FROM
                                        [Blog_Posts]
                                   WHERE
                                        [post_id]=@Id;";
            List<Post> list = Select(sql, new SqlParameter("@Id", postid));
            return list != null ? list[0] : null;
        }


        #endregion

        #region 选取文章总数
        /// <summary>
        /// 选取文章总数
        /// </summary>
        /// <returns>总数</returns>
        public static int SelectPostsCount()
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                    COUNT
                                    (
                                        [post_id]
                                    )
                                   FROM
                                        [Blog_Posts];";
            return (int)SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #endregion

        #region 私有操作方法

        #region ExecuteNonQuery
        /// <summary>
        /// 私有执行写入数据库方法
        /// </summary>
        /// <param name="post">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteNonQuery(Post post, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Alias", post.Alias),
                new SqlParameter("@Title", string.IsNullOrEmpty(post.Title) ? DBNull.Value : (object)post.Title),
                new SqlParameter("@SubTitle", string.IsNullOrEmpty(post.SubTitle) ? DBNull.Value : (object)post.SubTitle),
                new SqlParameter("@PubDate", string.IsNullOrEmpty(post.PubDate) ? DateTime.Now.ToString() : post.PubDate),
                new SqlParameter("@Update", string.IsNullOrEmpty(post.Update) ? DateTime.Now.ToString() : post.Update),
                new SqlParameter("@Content", post.Content),
                new SqlParameter("@CommenCount", post.CommentCount),
                new SqlParameter("@ViewCount", post.ViewCount),
                new SqlParameter("@AutherId", post.AutherId),
                new SqlParameter("@Status", post.Status),
                new SqlParameter("@ViewLevel", post.ViewLevel),
                new SqlParameter("@Password", string.IsNullOrEmpty(post.Password) ? DBNull.Value : (object)post.Password),
                new SqlParameter("@Id", post.Id)
                );
        }
        #endregion

        #region select
        private static List<Post> Select(string sql, params SqlParameter[] parameter)
        {
            List<Post> list = new List<Post>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                while (reader.Read())
                {
                    Post post = new Post(
                        reader.GetInt32(0),
                        reader.GetString(1),
                        reader.IsDBNull(2) ? null : reader.GetString(2),
                        reader.IsDBNull(3) ? null : reader.GetString(3),
                        reader.GetDateTime(4).ToString(),
                        reader.GetDateTime(5).ToString(),
                        reader.GetString(6),
                        reader.GetInt32(7),
                        reader.GetInt32(8),
                        reader.GetInt32(9),
                        reader.GetInt32(10),
                        reader.GetInt32(11),
                        reader.IsDBNull(12) ? null : reader.GetString(12)
                        );
                    list.Add(post);
                }
            }
            return list;
        }
        #endregion

        #endregion
    }
}
